WEBVTT 00:01.010 --> 00:04.840 In this video you will learn how to import financial data from an excel file. 00:05.060 --> 00:11.850 So in the whole upcoming section we will extensively work with the S&P 500 index which isn't easy read 00:11.930 --> 00:18.740 a summary or an aggregation of the 500 largest listed U.S. companies or in other words that actually 00:18.740 --> 00:24.470 aggregates or summarizes the performance of the 500 largest U.S. stocks. 00:24.470 --> 00:28.660 So later on the cost will have a deeper look into a stock and it says. 00:28.700 --> 00:35.840 But now for the time being we first need to import the S&P 500 price data from an excel file and this 00:35.840 --> 00:40.090 works pretty much in the same way as importing data from a CSP file. 00:40.610 --> 00:45.290 So instead of using the direct pan US method we'd see a sphere. 00:45.410 --> 00:48.280 We use surprise the read Excel method. 00:49.550 --> 00:56.810 Here we are in the Excel file and we can see S&P 500 price data starting from nineteen hundred seventy 00:57.180 --> 01:04.220 and to wireless today and we can also see here we have a column open high low close adjusted close and 01:04.220 --> 01:11.710 volume so this is a tabular data organization that we already know and that we also require here for 01:11.710 --> 01:20.080 append US data frame and we can simply import the tabular data into append us and we can do this with 01:20.170 --> 01:22.000 the read Excel method. 01:22.000 --> 01:27.610 Now we are back here now to took a notebook and first of all we need to import pandas as speedy as always 01:28.420 --> 01:36.540 and then we want to import our tabular data our S&P price data and we import the data from the file 01:36.720 --> 01:42.450 as S&P 500 Excel s so this is the file name of our excel file. 01:43.060 --> 01:47.770 And we simply have to pass to the file name to the direct Panda's method. 01:47.770 --> 01:48.670 The read excel. 01:48.670 --> 01:51.180 So let's do this and let's have a look here. 01:55.690 --> 01:57.700 And this looks actually quite familiar. 01:57.700 --> 02:03.780 So here we have the first column data starting in the year nineteen hundred seventy. 02:04.180 --> 02:11.300 And then we have the columns open high low close adjusted close and volume so here we have our date 02:11.300 --> 02:18.380 column and we all know that the best the data type would be a day time index and let's have a look here 02:18.440 --> 02:21.550 with the info method on some meta information 02:26.120 --> 02:31.840 and we can already see here in the date column that we have here the data type date time 64. 02:32.330 --> 02:38.720 So this is actually quite comfortable and Penthouse tries to adopt the excel data types as much as possible. 02:39.200 --> 02:42.730 So also an excel we have a date format. 02:42.890 --> 02:49.580 And typically when we import data and to append us with Excel then pen US tries to identify and can 02:49.580 --> 02:58.100 identify the data format in the Excel file and then us creates actually a daytime data type for the 02:58.100 --> 02:58.940 date column. 02:59.510 --> 03:05.990 So here we are back in the Excel file and already in the Excel file here the column date is actually 03:05.990 --> 03:14.020 has the format the data in Excel and the pen does successfully actually adopt that the data type here. 03:14.030 --> 03:19.280 So in this case there's actually no need to transform the data type of for the data column to a date 03:19.280 --> 03:19.920 time. 03:20.450 --> 03:24.370 But that's the functionality and the parameter to do so. 03:24.380 --> 03:31.180 And to be on the safe side you can also pass here that date column to the parameter past dates. 03:31.250 --> 03:35.690 So this works exactly in the same way as with the read the CSA year. 03:36.290 --> 03:41.990 And we already know that that might make sense to have for the date columnist or the index of our data 03:41.990 --> 03:50.060 frame by doing so we're creating a day time index and the consequently then we have a much higher functionality 03:50.060 --> 03:51.930 with a day time index. 03:52.040 --> 03:59.150 So also give us the read Excel method we have here the parameter index collar and TV pass date. 03:59.240 --> 04:00.470 So let's have a look here 04:03.490 --> 04:10.220 so now here we are on the left hand side to the day time index state with our time stamps. 04:10.390 --> 04:16.190 And as I said before this example so there was no need to pass them the date column to the past states 04:16.190 --> 04:21.620 per meter but to be on the safe side you should always use this here. 04:22.030 --> 04:28.000 So sometimes that might be the case that penned US cannot recognize a date column in excel. 04:28.000 --> 04:36.830 So we should always use service here and is actually another parameter in the read Excel method. 04:36.830 --> 04:39.560 And this is called the Youth course. 04:39.620 --> 04:43.820 And if we can define which columns we want to import from our excel file. 04:44.090 --> 04:51.840 So let's go back to our excel file and we can see now excel file that we have data from the Excel columns. 04:51.980 --> 04:59.640 A and to g inclusive and in the read Excel method we can actually define which of the columns. 04:59.660 --> 05:01.300 We want to impart. 05:01.310 --> 05:07.290 So now let's go back here to our Jupiter notebook and actually the default behavior is and that penned 05:07.290 --> 05:09.110 US imports all columns. 05:09.560 --> 05:15.710 So as you can see here if you didn't pass any information to the used class parameter and by default 05:16.080 --> 05:19.160 pandas actually imports you all Excel columns. 05:19.700 --> 05:22.750 So the default functionality is that we import all columns. 05:22.760 --> 05:25.010 And in this case it's from a to G. 05:25.460 --> 05:30.440 And actually we could explicitly pass here the columns a until G. 05:30.710 --> 05:37.940 And we have to do this here within quotation marks as a string and actually both ends are here inclusive. 05:37.940 --> 05:44.690 So by passing a total G to the youth cause parameter we are importing the columns a until G and both 05:44.690 --> 05:45.530 ends are inclusive. 05:45.530 --> 05:50.960 So let's throw this here and no surprise we get the same columns as here above. 05:50.960 --> 05:57.770 So all columns but we could also say that we are not interested in the columns adjusted close and volume. 05:57.770 --> 06:00.320 So let's go back to our excel file. 06:00.320 --> 06:07.850 So we only want to import the columns from a until E without the columns F and G and we can do this 06:08.390 --> 06:15.460 and instead of having from a until 2 year we pass here we want to f for the columns from a until e inclusive 06:17.270 --> 06:24.860 and if we can see now that we have only the columns um date open high low and close from the Excel file 06:24.920 --> 06:29.000 imported and actually the use cost parameters quite versatile. 06:29.450 --> 06:32.490 So we can also pass here as a string. 06:32.570 --> 06:34.940 The Excel column separated by a comma. 06:34.940 --> 06:43.380 So for example we want to f a then b then we also want to F C D and E. 06:43.460 --> 06:51.600 So let's have a look here so here we have the first five columns M date open high low close and of course 06:51.600 --> 06:57.090 we can also say that we do not need uh the column B so we can just delete it here. 06:59.530 --> 07:06.490 And we could also say that we want to have the column A and then the columns C tell e and we can do 07:06.490 --> 07:08.350 this here in this way. 07:08.440 --> 07:10.680 So this is no problem. 07:10.720 --> 07:13.130 So this gives us here the same output. 07:13.720 --> 07:19.870 And there's actually another parameter called the sheet name so let's go back to our excel file and 07:19.870 --> 07:25.110 we can see here at the bottom that actually our excel file consists of two sheets. 07:25.180 --> 07:32.740 So the very first sheet as of the S&P 500 sheet but we also have the sheet sales and we have some information 07:32.830 --> 07:34.630 on the sides of the sets. 07:34.630 --> 07:36.740 MAN MIKE Jim Stephen Joe and Tom. 07:37.030 --> 07:44.740 So let's go back here to our first see it so by for the read Excel method actually imparts the very 07:44.740 --> 07:47.500 first cheat and also zero based. 07:47.500 --> 07:48.980 And next thing applies here. 07:49.000 --> 07:55.260 So this is the first cheat at an exposition 0 and the Save sheet is set in exposition 1. 07:55.650 --> 08:01.090 And let's assume that actually you want to import the same sheet and we can do this. 08:01.450 --> 08:04.020 So that's an additional parameter sheet name. 08:04.200 --> 08:09.920 And if we can pass as a string the sheet name for example S&P 500. 08:10.480 --> 08:14.910 And alternatively we can also pass here the position of the sheet. 08:14.980 --> 08:20.740 So we have seen already that the sheet S&P 500 is at index positions zero. 08:22.870 --> 08:31.650 So this also works now let's try to import the safe sheet and typically pass either in position 1 08:34.730 --> 08:37.460 or alternatively we can pass the sheet name. 08:37.460 --> 08:38.240 Say it's 08:42.930 --> 08:43.770 all right. 08:44.400 --> 08:50.930 So we have decided now to import the S&P 500 data and we are only interested in the first column. 08:50.940 --> 08:58.110 So we are actually not interested in adjusted close and volume therefore we use here of pass here a 08:58.290 --> 09:04.290 to year to the youth course perimeter and then we save for the residing data frame and the variable 09:04.290 --> 09:05.670 S P 500. 09:05.700 --> 09:09.870 So let's do this here and let's have a look at the first five rows. 09:10.470 --> 09:16.050 So these are the first five rows and we can see here that our dataset starts at the end of the year 09:16.050 --> 09:17.940 nineteen hundred seventy year. 09:18.120 --> 09:24.660 So this this year the thirty first of December then we have January to nineteen hundred seventy one 09:24.690 --> 09:28.430 and actually we have fewer daily information. 09:28.590 --> 09:31.200 So let's have a look at the last five rows 09:33.720 --> 09:41.150 and here we have um the S&P price information to the very end of the year 2000 and 18. 09:41.190 --> 09:48.960 So the last business day and the lot last time stamp of the year 2018 is uh the twenty eighth of December 09:48.960 --> 09:50.280 to eighteen. 09:50.580 --> 09:59.130 And it's also have a look here at some information with the info method so we FCA a day time index with 09:59.280 --> 10:06.450 twelve thousand one hundred and seven entries and we have further four columns open high low and close 10:06.540 --> 10:09.060 where we have actually a float data types. 10:09.070 --> 10:16.440 So we have fair price information and actually now we can also export our data frame and create a new 10:16.450 --> 10:28.110 CSP file with the method to see a sphere and we call our new CSP file as P 500 dot CSP and we could 10:28.110 --> 10:36.210 also create a brand new excel file with them the method to excel and let's assume we name the new excel 10:36.210 --> 10:44.850 file s p 500 reduced because we reduced our S&P 500 data set by the last two columns. 10:44.850 --> 10:53.790 So the new excel file is now called S P 500 reduce dot Excel s so let's throw this here and consequently 10:53.790 --> 10:56.840 if you have a look at our photo we have now two new five. 10:56.870 --> 10:59.530 So once yes we file and one excel file. 11:00.000 --> 11:06.780 So this was a video on imparting financial data from an excel file and starting with the next video 11:06.810 --> 11:13.040 we are actually importing the S&P 500 data from the CSP file here. 11:13.080 --> 11:19.350 So this is the the way how we will do it but of course we could also import the S&P 500 data from the 11:19.350 --> 11:20.920 Excel file here. 11:20.970 --> 11:24.870 All right we are finished now and hope to see you also in the next videos by.